package com.bsoft.gol.hcs.satisfyStatic.dao;

import ctd.persistence.annotation.DAOMethod;
import ctd.persistence.annotation.DAOParam;
import ctd.persistence.support.hibernate.HibernateSupportDelegateDAO;
import ctd.persistence.support.hibernate.template.AbstractHibernateStatelessResultAction;
import ctd.persistence.support.hibernate.template.HibernateSessionTemplate;
import ctd.persistence.support.hibernate.template.HibernateStatelessResultAction;
import ctd.util.converter.ConversionUtils;
import org.atag.dao.action.QoHibernateStatelessResultAction;
import org.atag.dao.model.SimplePageInfo;
import org.hibernate.Query;
import org.hibernate.StatelessSession;
import org.hibernate.criterion.CriteriaSpecification;
import com.bsoft.gol.hcs.satisfyStatic.bean.qo.QuerySatisfySurveyStatisticListQo;
import com.bsoft.gol.hcs.satisfyStatic.bean.vo.QuerySatisfyStatisticSettingListVo;
import com.bsoft.gol.hcs.satisfyStatic.bean.vo.QuerySatisfySurveyStatisticListVo;
import com.bsoft.gol.hcs.satisfyStatic.entity.OdsSatisfyStatisticSetting;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Created by ChenJij on 2022/7/26.
 */
public abstract class OdsSatisfyStatisticSettingDAO extends HibernateSupportDelegateDAO<OdsSatisfyStatisticSetting> {
    public OdsSatisfyStatisticSettingDAO() {
        this.setKeyField("settingId");
    }

    @DAOMethod(sql = "from OdsSatisfyStatisticSetting where tenantId=:tenantId ")
    public abstract List<OdsSatisfyStatisticSetting> queryByTenantId(@DAOParam("tenantId") String tenantId);

    public List<QuerySatisfyStatisticSettingListVo> queryAllByTenantId(final String tenantId) {
        HibernateStatelessResultAction<List<QuerySatisfyStatisticSettingListVo>> action = new AbstractHibernateStatelessResultAction<List<QuerySatisfyStatisticSettingListVo>>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer sql = new StringBuffer();
                sql.append("select t.setting_id as settingId,t.setting_name as settingName from ods_satisfy_statistic_setting t where t.tenant_id=:tenantId order by t.create_time desc");
                Query query = ss.createSQLQuery(sql.toString());
                query.setParameter("tenantId", tenantId);
                query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                List<QuerySatisfyStatisticSettingListVo> titleBeanList = new ArrayList<QuerySatisfyStatisticSettingListVo>();
                List<Map> list = query.list();
                if (list.size() > 0) {
                    for (Map map : list) {
                        titleBeanList.add(ConversionUtils.convert(map, QuerySatisfyStatisticSettingListVo.class));
                    }
                    setResult(titleBeanList);
                } else {
                    setResult(null);
                }
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    /**
     * 查询满意度问卷统计列表
     */
    public SimplePageInfo<QuerySatisfySurveyStatisticListVo> querySatisfySurveyStatisticList(QuerySatisfySurveyStatisticListQo qo) {
        QoHibernateStatelessResultAction<QuerySatisfySurveyStatisticListVo, QuerySatisfySurveyStatisticListQo> action = getAction(qo,false);
        HibernateSessionTemplate.instance().execute(action);

        return action.getSimpleResultPage();
    }

    /**
     * 按题统计，满意度问卷统计列表
     */
    public SimplePageInfo<QuerySatisfySurveyStatisticListVo> querySatisfySurveyStatisticListByTitle(QuerySatisfySurveyStatisticListQo qo) {
        QoHibernateStatelessResultAction<QuerySatisfySurveyStatisticListVo, QuerySatisfySurveyStatisticListQo> action = getTitleAction(qo,false);
        HibernateSessionTemplate.instance().execute(action);

        return action.getSimpleResultPage();
    }

    /**
     * 查询满意度问卷统计合计
     */
    public QuerySatisfySurveyStatisticListVo getSatisfySurveyStatisticTotal(QuerySatisfySurveyStatisticListQo qo) {
        QoHibernateStatelessResultAction<QuerySatisfySurveyStatisticListVo, QuerySatisfySurveyStatisticListQo> action = getAction(qo,true);
        HibernateSessionTemplate.instance().execute(action);
        SimplePageInfo<QuerySatisfySurveyStatisticListVo> simpleResultPage = action.getSimpleResultPage();
        return simpleResultPage.getList().get(0);
    }

    /**
     * 按题统计，查询满意度问卷统计合计
     */
    public QuerySatisfySurveyStatisticListVo getSatisfySurveyStatisticTotalByTitle(QuerySatisfySurveyStatisticListQo qo) {
        QoHibernateStatelessResultAction<QuerySatisfySurveyStatisticListVo, QuerySatisfySurveyStatisticListQo> action = getTitleAction(qo,true);
        HibernateSessionTemplate.instance().execute(action);
        SimplePageInfo<QuerySatisfySurveyStatisticListVo> simpleResultPage = action.getSimpleResultPage();
        return simpleResultPage.getList().get(0);
    }

    public QoHibernateStatelessResultAction<QuerySatisfySurveyStatisticListVo, QuerySatisfySurveyStatisticListQo> getAction(QuerySatisfySurveyStatisticListQo qo,boolean totalFlag){
        QoHibernateStatelessResultAction<QuerySatisfySurveyStatisticListVo, QuerySatisfySurveyStatisticListQo> action
                = new QoHibernateStatelessResultAction<>(true, qo, QuerySatisfySurveyStatisticListVo.class);
        String sql;
        //查询合计
        if(totalFlag){
            sql = "select " +
                    "  {{ " +
                    "  \"\"                   as optionId, " +
                    "  \"\"                   as typeName, " +
                    "  \"100\"                   as proportion, " +
                    "  sum(i.surveyPersonNumber)   as surveyPersonNumber, " +
                    "  sum(i.planNumber)     as planNumber, " +
                    "  sum(i.afterHosNumber) as afterHosNumber, " +
                    "  sum(i.scanCodeNumber) as scanCodeNumber, " +
                    "  sum(i.abnormalNumber) as abnormalNumber" +
                    "  }} from (";
        }else {
            //查询维度分组
            sql = "select {{*}} from (";
        }
        action.setSql(sql);
        action.addSql("select " +
                "  f.option_id   as optionId, " +
                "  f.answer_name AS typeName, " +
                "  ifnull(f.surveyPersonNumber,0) surveyPersonNumber, " +
                "  ifnull(g.planNumber,0) planNumber, " +
                "  ifnull(g.afterHosNumber,0) afterHosNumber, " +
                "  ifnull(g.scanCodeNumber,0) scanCodeNumber, " +
                "  ifnull(h.abnormalNumber,0) abnormalNumber "+
                "  from ( " +
                "       select " +
                "         b.option_id, " +
                "         b.answer_name, " +
                "         count(distinct b.patient_survey_id) surveyPersonNumber " +
                "       from patient_satisfaction_survey a, patient_satisfaction_survey_answer b " +
                "       where  a.patient_survey_id = b.patient_survey_id ");
        action.addCondition(" and b.questionnaire_id =:questionnaireId ");
        action.addCondition(" and b.title_id =:titleId ");
        action.addCondition(" and b.option_id =:optionId ");
        action.addCondition(" and a.survey_time >=:beginDate ");
        action.addCondition(" and a.survey_time <=:endDate ");
        action.addCondition(" and a.organization_id =:organizationId ");
        action.addCondition(" and a.survey_source =:surveySource ");
        action.addSql(" group by b.option_id) f left join " +
                "  ( " +
                "    SELECT " +
                "      c.option_id, " +
                "      group_concat( " +
                "          CASE (c.survey_source) " +
                "          WHEN '1' " +
                "            THEN " +
                "              c.sourceCount " +
                "          END " +
                "      ) planNumber, " +
                "      group_concat( " +
                "          CASE (c.survey_source) " +
                "          WHEN '2' " +
                "            THEN " +
                "              c.sourceCount " +
                "          END " +
                "      ) afterHosNumber, " +
                "      group_concat( " +
                "          CASE (c.survey_source) " +
                "          WHEN '3' " +
                "            THEN " +
                "              c.sourceCount " +
                "          END " +
                "      ) scanCodeNumber " +
                "    from ( " +
                "           select " +
                "             b.option_id, " +
                "             b.answer_name, " +
                "             a.survey_source, " +
                "             count(a.survey_source) sourceCount " +
                "           from patient_satisfaction_survey a, patient_satisfaction_survey_answer b " +
                "           where  a.patient_survey_id = b.patient_survey_id ");

        action.addCondition(" and b.questionnaire_id =:questionnaireId ");
        action.addCondition(" and b.title_id =:titleId ");
        action.addCondition(" and b.option_id =:optionId ");
        action.addCondition(" and a.survey_time >=:beginDate ");
        action.addCondition(" and a.survey_time <=:endDate ");
        action.addCondition(" and a.organization_id =:organizationId ");
        action.addCondition(" and a.survey_source =:surveySource ");
        action.addSql("           group by b.option_id, a.survey_source) c " +
                "    group by c.option_id) g on f.option_id = g.option_id " +
                "  left join " +
                "  ( " +
                "    select " +
                "      b.option_id, " +
                "      b.answer_name, " +
                "      count(distinct a.patient_survey_id) abnormalNumber " +
                "    from patient_satisfaction_survey a, patient_satisfaction_survey_answer b " +
                "    where  a.patient_survey_id = b.patient_survey_id  and b.answer_abnormal='1' ");
        action.addCondition(" and b.questionnaire_id =:questionnaireId ");
        action.addCondition(" and b.option_id =:optionId ");
        action.addCondition(" and a.survey_time >=:beginDate ");
        action.addCondition(" and a.survey_time <=:endDate ");
        action.addCondition(" and a.organization_id =:organizationId ");
        action.addCondition(" and a.survey_source =:surveySource ");
        action.addCondition(" and b.title_id =:titleId ");
        action.addSql("   group by b.option_id) h on g.option_id = h.option_id");
        action.addSql("   ) i ");
        return action;
    }

    public QoHibernateStatelessResultAction<QuerySatisfySurveyStatisticListVo, QuerySatisfySurveyStatisticListQo> getTitleAction(QuerySatisfySurveyStatisticListQo qo,boolean totalFlag){
        QoHibernateStatelessResultAction<QuerySatisfySurveyStatisticListVo, QuerySatisfySurveyStatisticListQo> action
                = new QoHibernateStatelessResultAction<>(true, qo, QuerySatisfySurveyStatisticListVo.class);
        String sql;
        //查询合计
        if(totalFlag){
            sql = "select " +
                    "  {{ " +
                    "  \"\"                   as optionId, " +
                    "  \"\"                   as typeName, " +
                    "  \"100\"                   as proportion, " +
                    "  sum(i.surveyPersonNumber)   as surveyPersonNumber, " +
                    "  sum(i.planNumber)     as planNumber, " +
                    "  sum(i.afterHosNumber) as afterHosNumber, " +
                    "  sum(i.scanCodeNumber) as scanCodeNumber, " +
                    "  sum(i.abnormalNumber) as abnormalNumber" +
                    "  }} from (";
        }else {
            //查询维度分组
            sql = "select {{*}} from (";
        }
        action.setSql(sql);
        action.addSql("select " +
                "  f.title_id   as optionId, " +
                "  f.title_name AS typeName, " +
                "  ifnull(f.surveyPersonNumber,0) surveyPersonNumber, " +
                "  ifnull(g.planNumber,0) planNumber, " +
                "  ifnull(g.afterHosNumber,0) afterHosNumber, " +
                "  ifnull(g.scanCodeNumber,0) scanCodeNumber, " +
                "  ifnull(h.abnormalNumber,0) abnormalNumber "+
                "  from ( " +
                "       select " +
                "         b.title_id, " +
                "         b.title_name, " +
                "         count(distinct b.patient_survey_id) surveyPersonNumber " +
                "       from patient_satisfaction_survey a, patient_satisfaction_survey_answer b " +
                "       where  a.patient_survey_id = b.patient_survey_id and b.grade is not null ");
        action.addCondition(" and b.questionnaire_id =:questionnaireId ");
        action.addCondition(" and b.title_id =:titleId ");
        action.addCondition(" and b.option_id =:optionId ");
        action.addCondition(" and a.survey_time >=:beginDate ");
        action.addCondition(" and a.survey_time <=:endDate ");
        action.addCondition(" and a.organization_id =:organizationId ");
        action.addCondition(" and a.survey_source =:surveySource ");
        action.addSql(" group by b.title_id) f left join " +
                "  ( " +
                "    SELECT " +
                "      c.title_id, " +
                "      group_concat( " +
                "          CASE (c.survey_source) " +
                "          WHEN '1' " +
                "            THEN " +
                "              c.sourceCount " +
                "          END " +
                "      ) planNumber, " +
                "      group_concat( " +
                "          CASE (c.survey_source) " +
                "          WHEN '2' " +
                "            THEN " +
                "              c.sourceCount " +
                "          END " +
                "      ) afterHosNumber, " +
                "      group_concat( " +
                "          CASE (c.survey_source) " +
                "          WHEN '3' " +
                "            THEN " +
                "              c.sourceCount " +
                "          END " +
                "      ) scanCodeNumber " +
                "    from ( " +
                "           select " +
                "             b.title_id, " +
                "             b.title_name, " +
                "             a.survey_source, " +
                "             count(a.survey_source) sourceCount " +
                "           from patient_satisfaction_survey a, patient_satisfaction_survey_answer b " +
                "           where  a.patient_survey_id = b.patient_survey_id ");

        action.addCondition(" and b.questionnaire_id =:questionnaireId ");
        action.addCondition(" and b.title_id =:titleId ");
        action.addCondition(" and b.option_id =:optionId ");
        action.addCondition(" and a.survey_time >=:beginDate ");
        action.addCondition(" and a.survey_time <=:endDate ");
        action.addCondition(" and a.organization_id =:organizationId ");
        action.addCondition(" and a.survey_source =:surveySource ");
        action.addSql("           group by b.title_id, a.survey_source) c " +
                "    group by c.title_id) g on f.title_id = g.title_id " +
                "  left join " +
                "  ( " +
                "    select " +
                "      b.title_id, " +
                "      b.title_name, " +
                "      count(distinct a.patient_survey_id) abnormalNumber " +
                "    from patient_satisfaction_survey a, patient_satisfaction_survey_answer b " +
                "    where  a.patient_survey_id = b.patient_survey_id  and b.answer_abnormal='1' ");
        action.addCondition(" and b.questionnaire_id =:questionnaireId ");
        action.addCondition(" and b.option_id =:optionId ");
        action.addCondition(" and a.survey_time >=:beginDate ");
        action.addCondition(" and a.survey_time <=:endDate ");
        action.addCondition(" and a.organization_id =:organizationId ");
        action.addCondition(" and a.survey_source =:surveySource ");
        action.addCondition(" and b.title_id =:titleId  and b.answer_abnormal = '1' ");
        action.addSql("   group by b.title_id) h on g.title_id = h.title_id");
        action.addSql("   ) i ");
        return action;
    }

    /**
     * 查询满意度问卷统计分数列表
     */
    public List<QuerySatisfySurveyStatisticListVo.ClassifyScoreList> queryClassifyScoreList(QuerySatisfySurveyStatisticListQo qo) {
        QoHibernateStatelessResultAction<QuerySatisfySurveyStatisticListVo.ClassifyScoreList, QuerySatisfySurveyStatisticListQo> action = getClassifyScoreListAction(qo,false);
        HibernateSessionTemplate.instance().execute(action);
        return action.getResultList();
    }
    /**
     * 按题统计，查询满意度问卷统计分数列表
     */
    public List<QuerySatisfySurveyStatisticListVo.ClassifyScoreList> queryClassifyScoreListByTitle(QuerySatisfySurveyStatisticListQo qo) {
        QoHibernateStatelessResultAction<QuerySatisfySurveyStatisticListVo.ClassifyScoreList, QuerySatisfySurveyStatisticListQo> action = getClassifyScoreListByTitleAction(qo,false);
        HibernateSessionTemplate.instance().execute(action);
        return action.getResultList();
    }

    public QoHibernateStatelessResultAction<QuerySatisfySurveyStatisticListVo.ClassifyScoreList, QuerySatisfySurveyStatisticListQo> getClassifyScoreListAction(QuerySatisfySurveyStatisticListQo qo,boolean totalFlag){
        QoHibernateStatelessResultAction<QuerySatisfySurveyStatisticListVo.ClassifyScoreList, QuerySatisfySurveyStatisticListQo> action
                = new QoHibernateStatelessResultAction<>(false, qo, QuerySatisfySurveyStatisticListVo.ClassifyScoreList.class);
        String sql;
        //查询合计
        if(totalFlag){
            sql = "select " +
                    "  {{ " +
                    "  i,optionId , " +
                    "  avg(i.classifyScore)   as classifyScore, " +
                    "  avg(i.standardDeviationScore) as standardDeviationScore " +
                    "  }} from (";
        }else {
            //查询维度分组
            sql = "select {{*}} from (";
        }
        action.setSql(sql);
        action.addSql("select " +
                "  m.option_id optionId, " +
                "  ifnull(AVG(n.grade),0) classifyScore, " +
                "  ifnull(STDDEV_SAMP(n.grade),0) standardDeviationScore " +
                " from patient_satisfaction_survey_answer n LEFT JOIN ( " +
                "      select " +
                "      b.option_id, " +
                "      b.patient_survey_id, " +
                "      h.organization_id, " +
                "      h.survey_time, " +
                "      h.survey_source " +
                "      from patient_satisfaction_survey_answer b ,patient_satisfaction_survey h " +
                "      where 1=1 and b.patient_survey_id=h.patient_survey_id ");
        action.addCondition(" and b.questionnaire_id =:questionnaireId  ");
        action.addCondition(" and b.title_id =:titleId ");
        action.addCondition(" and b.option_id =:optionId ");
        action.addSql("      GROUP BY option_id, patient_survey_id) m on n.patient_survey_id = m.patient_survey_id ");
        action.addCondition(" where n.title_id in (:titleIdList) ");
        action.addCondition(" and m.survey_time >=:beginDate ");
        action.addCondition(" and m.survey_time <=:endDate ");
        action.addCondition(" and m.organization_id =:organizationId ");
        action.addCondition(" and m.survey_source =:surveySource ");
        action.addSql(" GROUP BY m.option_id ) i ");
        return action;
    }


    public QoHibernateStatelessResultAction<QuerySatisfySurveyStatisticListVo.ClassifyScoreList, QuerySatisfySurveyStatisticListQo> getClassifyScoreListByTitleAction(QuerySatisfySurveyStatisticListQo qo,boolean totalFlag){
        QoHibernateStatelessResultAction<QuerySatisfySurveyStatisticListVo.ClassifyScoreList, QuerySatisfySurveyStatisticListQo> action
                = new QoHibernateStatelessResultAction<>(false, qo, QuerySatisfySurveyStatisticListVo.ClassifyScoreList.class);
        String sql = "select {{*}} from (";
        action.setSql(sql);
        action.addSql("select " +
                "  a.title_id optionId, " +
                "  ifnull(avg(a.grade),0)         classifyScore, " +
                "  ifnull(max(a.grade),0)         maxScore, " +
                "  ifnull(min(a.grade),0)         minScore, " +
                "  ifnull(STDDEV_SAMP(a.grade),0) standardDeviationScore " +
                "from patient_satisfaction_survey_answer a,patient_satisfaction_survey h where 1=1 and a.grade is not null and a.patient_survey_id=h.patient_survey_id ");
        action.addCondition(" and  a.questionnaire_id =:questionnaireId ");
        action.addCondition(" and h.organization_id =:organizationId ");
        action.addCondition(" and h.survey_time >=:beginDate ");
        action.addCondition(" and h.survey_time <=:endDate ");
        action.addCondition(" and h.survey_source =:surveySource ");


        action.addSql("group by a.title_id ) i ");
        return action;
    }


    public QuerySatisfySurveyStatisticListVo.ClassifyScoreList queryMedianByTitleId(final String titleId) {
        HibernateStatelessResultAction<QuerySatisfySurveyStatisticListVo.ClassifyScoreList> action = new AbstractHibernateStatelessResultAction<QuerySatisfySurveyStatisticListVo.ClassifyScoreList>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer sql = new StringBuffer();
                sql.append("SELECT " +
                        " t1.title_id as titleId, " +
                        " avg( t1.grade ) AS median  " +
                        "FROM " +
                        " ( " +
                        " SELECT " +
                        "  d.title_id, " +
                        "  (@rownum\\:=@rownum + 1) AS row_number, " +
                        "  d.grade  " +
                        " FROM " +
                        "  patient_satisfaction_survey_answer d, " +
                        "  ( SELECT @rownum\\:=0 ) r  " +
                        " WHERE " +
                        "  1 = 1  " +
                        "  AND d.title_id =:titleId  " +
                        "  AND d.grade IS NOT NULL  " +
                        " ORDER BY " +
                        "  d.grade  " +
                        " ) AS t1, " +
                        " ( SELECT count(*) AS total_rows FROM patient_satisfaction_survey_answer d WHERE 1 = 1 AND d.grade IS NOT NULL AND d.title_id =:titleId ) AS t2  " +
                        "WHERE " +
                        " 1  " +
                        " AND t1.row_number IN ( floor(( total_rows + 1 )/ 2 ), floor(( total_rows + 2 )/ 2 ) )");
                Query query = ss.createSQLQuery(sql.toString());
                query.setParameter("titleId", titleId);
                query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                List<QuerySatisfySurveyStatisticListVo.ClassifyScoreList> classifyScoreLists = new ArrayList<QuerySatisfySurveyStatisticListVo.ClassifyScoreList>();
                List<Map> list = query.list();
                if (list.size() > 0) {
                    for (Map map : list) {
                        classifyScoreLists.add(ConversionUtils.convert(map, QuerySatisfySurveyStatisticListVo.ClassifyScoreList.class));
                    }
                    setResult(classifyScoreLists.get(0));
                } else {
                    setResult(null);
                }
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

}